/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Domain;

import Data.DBAccess;
import config.ServerConfig;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.security.auth.callback.Callback;

/**
* Integer: All of them must have, at least, 4 digits.
* Varchar2(x): It must contain exactly x/2 characters.
* Number(x,y): It must contain, at least, x/2 digits.
* Date: A random date (the Oracle format is ‘12/12/01’ by default).
 */
public class OracleWorkFlow {

    
    
    Random r = new Random();

    private DBAccess access= new DBAccess();
    private ServerConfig config;
    private int minCustomer;
    private int minNation;
    private int minOrders;
    private int minPart;
    private int minRegion;
    private int minSupplier;
    private ArrayList<Parell> PartSuppArray;
    
    private static OracleWorkFlow instance = null;
    protected OracleWorkFlow() {
        PartSuppArray = new ArrayList<Parell>();
        obtenerMinimos();
        config = ServerConfig.getInstance();
      // Exists only to defeat instantiation.
   }
   public static OracleWorkFlow getInstance() {
      if(instance == null) {
         instance = new OracleWorkFlow();
      }
      return instance;
   }
            /**
     *  R_REGIONKEY identifier 5 regions are populated
        R_NAME fixed text, size 25
        R_COMMENT variable text, size 152
        Primary Key: R_REGIONKEY
     * @return 
     */
    public Integer InsertRegion(boolean truncate){
        Integer counter = 0;
        java.sql.PreparedStatement region;
        /*if(truncate) {
            Statement m;
            try {
                m = access.getCon().createStatement();
                System.out.println(m.executeUpdate("truncate table region"));
                access.getCon().commit();
                minRegion = 1;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
        }*/
        region = access.getRegion();
        for(int i = minRegion; i < minRegion + config.N_Region; i++) {
            try {
                //N_NATIONKEY{1..5)
                region.setInt(1, i);
                region.setString(2, generateRandomString((64)/2));
                region.setString(3, generateRandomString(160/2));
                region.setString(4, generateRandomString(config.C_SKIP));
                region.addBatch();
                counter++;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        try {
            region.executeBatch();
            access.getCon().commit();
            minRegion +=config.N_Region;
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
        return counter;
    }
    
    /**
    Column Name Datatype Requirements Comment
    N_NATIONKEY identifier 25 nations are populated
    N_NAME fixed text, size 25
    N_REGIONKEY identifier Foreign Key to R_REGIONKEY
    N_COMMENT variable text, size 152
    Primary Key: N_NATIONKEY
    * */
    
    public Integer InsertNation(boolean truncate) {
        java.sql.PreparedStatement nation = access.getInsertNation();
        Integer counter = 0;
        /*if(truncate){
            Statement m;
            try {
                m = access.getCon().createStatement();
                System.out.println(m.executeUpdate("truncate table nation"));
                access.getCon().commit();
                minNation = 1;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
        }*/
        for(int i = minNation; i < minNation +config.N_Nation; i++ ){
            try {
                //N_NationKey {1..25}
                nation.setInt(1, i);
                nation.setString(2, generateRandomString(64/2));
                int region = r.nextInt(minRegion-1)+1;
                //2System.out.println(region);
                nation.setInt(3, region);
                nation.setString(4, generateRandomString(160/2));
                nation.setString(5, generateRandomString(config.C_SKIP));
                nation.addBatch();
                counter++;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        try {
            nation.executeBatch();
            access.getCon().commit();
            minNation += config.N_Nation;
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
        return counter;
    }

        /**
            CUSTOMER Table Layout
            Column Name Datatype Requirements Comment
            C_CUSTKEY Identifier SF*150,000 are populated
            C_NAME variable text, size 25
            C_ADDRESS variable text, size 40
            C_NATIONKEY Identifier Foreign Key to N_NATIONKEY
            C_PHONE fixed text, size 15
            C_ACCTBAL Decimal
            C_MKTSEGMENT fixed text, size 10
            C_COMMENT variable text, size 117
            );
        */
    public Integer insertIntoCustomer(boolean truncate) {
        int counter = 0;
        try {
           /* if(truncate) {
                Statement m;
                try {
                    m = access.getCon().createStatement();
                    System.out.println(m.executeUpdate("truncate table Customer"));
                    access.getCon().commit();
                    minCustomer = 1;
                } catch (SQLException ex) {
                    Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
                }
            }*/
            java.sql.PreparedStatement customer = access.getInsertCustomer();
            for(int i = minCustomer; i <minCustomer+ config.N_Customer; i++)
            {
                //C_CUSTOMERKEY {1...150000}
                customer.setInt(1, i);
                Random r = new Random();
                customer.setString(2, generateRandomString(64/2));
                customer.setString(3, generateRandomString(64/2));
                //TODO nationkey
                Integer nation =  r.nextInt(minNation-1)+1;
                customer.setInt(4, nation);
                customer.setString(5, generateRandomString(64/2));
                
                customer.setFloat(6, r.nextFloat()+1000000);
                customer.setString(7, generateRandomString(64/2));
                customer.setString(8, generateRandomString(120/2));
                customer.setString(9, generateRandomString(config.C_SKIP));
                customer.addBatch();
            }
            customer.executeBatch();
            access.getCon().commit();
            minCustomer += config.N_Customer;
            
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
        return counter;
    }
    /**
        O_ORDERKEY Identifier SF*1,500,000 are sparsely populated
        O_CUSTKEY Identifier Foreign Key to C_CUSTKEY
        O_ORDERSTATUS fixed text, size 1
        O_TOTALPRICE Decimal
        O_ORDERDATE Date
        O_ORDERPRIORITY fixed text, size 15
        O_CLERK fixed text, size 15
        O_SHIPPRIORITY Integer
        O_COMMENT variable text, size 79
        Primary Key: O_ORDERKEY
     */
    
    public Integer insertOrders(boolean truncate) {
        Integer counter = 0;
        /*if(truncate) {
            Statement m;
               try {
                   m = access.getCon().createStatement();
                   System.out.println(m.executeUpdate("truncate table Orders"));
                   access.getCon().commit();
                   minOrders =1;
               } catch (SQLException ex) {
                   Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
               }
        }*/
        try {
            java.sql.PreparedStatement orders = access.getOrders();
            for(int i = minOrders; i < minOrders +config.N_Orders; i++) {
                orders.setInt(1, i);
                //Foreing key de Customer acotado entre {1..N_Customers}
                orders.setInt(2, r.nextInt(minCustomer-1)+1);
                orders.setString(3,generateRandomString(64/2));
                orders.setFloat(4, r.nextFloat()+1000000);
                Calendar cal = Calendar.getInstance();
                java.sql.Date d = new Date(cal.getTimeInMillis());
                //System.out.println("        "+d.getMonth());
                String date = generateRandomDate2(d.getDay(), d.getMonth(), r.nextInt(12)+1);
                orders.setDate(5, d);
                orders.setString(6, generateRandomString(15/2));
                orders.setString(7, generateRandomString(64/2));
                orders.setInt(8, r.nextInt()+1000);
                orders.setString(9, generateRandomString(80/2));
                orders.setString(10, generateRandomString(config.C_SKIP));
                orders.addBatch();
                counter ++;
                /*if(counter%500 == 0)
                    System.out.println("ORDERS: "+counter);*/
            }
            orders.executeBatch();
            access.getCon().commit();
            minOrders += config.N_Orders;
        }
        catch(SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);

        }
        
        return counter;   
    }
    
    /**
        P_PARTKEY identifier SF*200,000 are populated
        P_NAME variable text, size 55
        P_MFGR fixed text, size 25TPC BenchmarkTM H Standard Specification Revision 2.15.0 Page 15
        P_BRAND fixed text, size 10
        P_TYPE variable text, size 25
        P_SIZE integer
        P_CONTAINER fixed text, size 10
        P_RETAILPRICE decimal
        P_COMMENT variable text, size 23
        Primary Key: P_PARTKEY
     */
    
    public Integer insertParts(boolean truncate) {
       Integer counter = 0;
       /*if (truncate) {
        Statement m;
           try {
               m = access.getCon().createStatement();
               System.out.println(m.executeUpdate("truncate table Part"));
               access.getCon().commit();
               minPart = 1;
           } catch (SQLException ex) {
               Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
           }
       }*/
        try {
            java.sql.PreparedStatement parts = access.getPart();
            for(int i = minPart; i < minPart +config.N_Part; i++) {
                parts.setInt(1, i);
                
                parts.setString(2, generateRandomString(64/2));
                parts.setString(3, generateRandomString(64/2));
                parts.setString(4, generateRandomString(64/2));
                parts.setString(5, generateRandomString(64/2));
                
                parts.setInt(6, r.nextInt()+1000);
                
                parts.setString(7, generateRandomString(64/2));
                
                Double f = r.nextDouble()+100000;
                
                parts.setDouble(8, f);
                parts.setString(9, generateRandomString(64/2));
                parts.setString(10, generateRandomString(config.C_SKIP));
                parts.addBatch();
                counter ++;
            }
            parts.executeBatch();
            access.getCon().commit();
            minPart += config.N_Part;
        }
        catch(SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);

        }
        
        return counter;   
    }
    
    /**
        S_SUPPKEY identifier SF*10,000 are populated
        S_NAME fixed text, size 25
        S_ADDRESS variable text, size 40
        S_NATIONKEY Identifier Foreign Key to N_NATIONKEY
        S_PHONE fixed text, size 15
        S_ACCTBAL decimal
        S_COMMENT variable text, size 101
        Primary Key: S_SUPPKEY
     */
    
    public Integer insertSuppliers(Boolean truncate) {
        Integer counter = 0;
        java.sql.PreparedStatement supplier;
        /*if(truncate) {
            Statement m;
            try {
                m = access.getCon().createStatement();
                System.out.println(m.executeUpdate("truncate table Supplier"));
                access.getCon().commit();
                minSupplier = 1;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
        }*/
        supplier = access.getSupplier();

        for(int i = minSupplier; i <minSupplier+ config.N_Supplier; i++) {
            try {
                //N_NATIONKEY{1..5)
                supplier.setInt(1, i);
                supplier.setString(2, generateRandomString(64/2));
                supplier.setString(3, generateRandomString(64/2));
                supplier.setInt(4, r.nextInt(minNation-1)+1);
                supplier.setString(5, generateRandomString(18/2));
                supplier.setFloat(6, r.nextFloat()+1000000);
                supplier.setString(7, generateRandomString(105/2));
                supplier.setString(8, generateRandomString(config.C_SKIP));
                supplier.addBatch();
                counter++;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        try {
            supplier.executeBatch();
            access.getCon().commit();
            minSupplier += config.N_Supplier;
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
        return counter;
    }
    
    /**
        Column Name Datatype Requirements Comment
        PS_PARTKEY Identifier Foreign Key to P_PARTKEY
        PS_SUPPKEY Identifier Foreign Key to S_SUPPKEY
        PS_AVAILQTY integer
        PS_SUPPLYCOST Decimal
        PS_COMMENT variable text, size 199
        Primary Key: PS_PARTKEY, PS_SUPPKEY
     */
    
    public Integer insertPartSupp(Boolean truncate) {
        Integer counter = 0;
        java.sql.PreparedStatement partSupp;
        /*if(truncate) {
            Statement m;
            try {
                m = access.getCon().createStatement();
                System.out.println(m.executeUpdate("truncate table PartSupp"));
                access.getCon().commit();
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
        }*/
        int aux = 0;
        partSupp = access.getPartsupp();

        for(int i = minPart-config.N_Part; i < minPart && aux < config.N_PartsUpp; i++) {
            for(int j = minSupplier-config.N_Supplier; j< minSupplier && aux < config.N_PartsUpp; j++) {
                try {
                    //N_NATIONKEY{1..5)
                    partSupp.setInt(1, i);
                    partSupp.setInt(2, j);
                    
                    PartSuppArray.add(new Parell(i,j));
                    
                    partSupp.setInt(3, r.nextInt()+1000);
                    partSupp.setFloat(4, r.nextFloat()+1000000);
                    partSupp.setString(5, generateRandomString(200/2));
                    partSupp.setString(6, generateRandomString(config.C_SKIP));
                    partSupp.addBatch();
                    aux++;                        
                    counter++;
                    /*if(counter%500 == 0)
                        System.out.println("    PARTSUPP :"+counter);*/
                } catch (SQLException ex) {
                    Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        try {
            partSupp.executeBatch();
            access.getCon().commit();
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
        return counter; 
    }
    
    
    /**
        L_ORDERKEY identifier Foreign Key to O_ORDERKEY
        L_PARTKEY identifier Foreign key to P_PARTKEY, first part of the 
                compound Foreign Key to (PS_PARTKEY, PS_SUPPKEY) with L_SUPPKEY
        L_SUPPKEY Identifier Foreign key to S_SUPPKEY, 
        *       second part of the compound Foreign Key to 
        *       (PS_PARTKEY, TPC BenchmarkTM H Standard Specification Revision 2.15.0 Page 17
        *       PS_SUPPKEY) with L_PARTKEY
        L_LINENUMBER integer
        L_QUANTITY decimal
        L_EXTENDEDPRICE decimal
        L_DISCOUNT decimal
        L_TAX decimal
        L_RETURNFLAG fixed text, size 1
        L_LINESTATUS fixed text, size 1
        L_SHIPDATE date
        L_COMMITDATE date
        L_RECEIPTDATE date
        L_SHIPINSTRUCT fixed text, size 25
        L_SHIPMODE fixed text, size 10
        L_COMMENT variable text size 44
        Primary Key: L_ORDERKEY, L_LINENUMBER
     */
    public Integer inserLineItem(Boolean truncate) {
        /*if(truncate) {
            Statement m;
            try {
                m = access.getCon().createStatement();
                System.out.println(m.executeUpdate("truncate table lineitem"));
                access.getCon().commit();
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
        }*/
        Integer counter = 0;
        java.sql.PreparedStatement lineItem;
        lineItem = access.getInsertlineitem();

        int pSupKey = minPart-config.N_Part;
        int pPartKey = minSupplier-config.N_Supplier;

        for(int i = minOrders-config.N_Orders; i < minOrders; i++) {
            for(int j = 1000; j < 1004; j++) {
                 try {
                    
                    lineItem.setInt(1, i);
                    Parell p = PartSuppArray.get(r.nextInt(PartSuppArray.size()));
                    lineItem.setInt(2, p.part);
                    lineItem.setInt(3, p.supp);
                    lineItem.setInt(4, j);
                    
                    lineItem.setInt(5, r.nextInt()+1000);
                    
                    lineItem.setFloat(6, r.nextFloat()+1000000);
                    lineItem.setFloat(7, r.nextFloat()+1000000);
                    lineItem.setFloat(8, r.nextFloat()+1000000);
                    
                    lineItem.setString(9, generateRandomString(64/2));
                    lineItem.setString(10, generateRandomString(64/2));

                    Calendar cal = Calendar.getInstance();
                    java.sql.Date d = new Date(cal.getTimeInMillis());
      
                    Integer year = r.nextInt(12)+1;
                    /*
                    lineItem.setString(11, generateRandomDate2(d.getDay(), d.getMonth(), year));
                    lineItem.setString(12, generateRandomDate2(d.getDay()+1, d.getMonth(), year));
                    lineItem.setString(13, generateRandomDate2(1, d.getMonth()+1, year));
                    */
                    lineItem.setDate(11, d);
                    d.setDate(d.getDate()+1);
                    lineItem.setDate(12, d);
                    d.setDate(d.getMonth()+1);
                    lineItem.setDate(13, d);
  
                    
                    lineItem.setString(14, generateRandomString(64/2));
                    lineItem.setString(15, generateRandomString(64/2));
                    lineItem.setString(16, generateRandomString(64/2));

                    lineItem.setString(17, generateRandomString(config.C_SKIP));
                    lineItem.addBatch();
                    //lineItem.executeUpdate();
                    counter++;
                    /*if(counter%500 == 0)
                        System.out.println("    LINEITEM :"+counter);*/
                } catch (SQLException ex) {
                    Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        try {
            lineItem.executeBatch();
            access.getCon().commit();
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }

        return counter;
    }
    
    /**
     * 
     * @param lenght
     * @return 
     */
    public long executeQuery1(int times) {
        long minTime = 0;
        
        java.sql.PreparedStatement query1 = access.getSelect1();
        for (int i=0; i < times; i++) {
            try {
                long time = System.currentTimeMillis();
                Date d = new Date(Calendar.getInstance().getTimeInMillis());
                query1.setDate(1, d);
                //query1.setString(1, "12/12/12");
                ResultSet rs = query1.executeQuery();
                /*while(rs.next()) {
                    System.out.println(rs.getObject(1));
                            
                }*/
                long finalTime = System.currentTimeMillis()-time;
                if(minTime == 0)
                    minTime = finalTime;
                else if(minTime >finalTime)
                    minTime = finalTime;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return minTime;
    }
    public long executeQuery2(int times) {
        long minTime = 0;
        Statement s;
        int size = 0;
        String region ="";
        String type = "";
        try {
            s = access.getCon().createStatement();
            int aux = r.nextInt(minPart);
            ResultSet rs = s.executeQuery("SELECT * FROM part WHERE P_PartKey ="+aux+"");
            if(rs.next()) {
                size = rs.getInt(6);
                type = rs.getString(5);
            }
            s = access.getCon().createStatement();
            aux = r.nextInt(minRegion);
            rs = s.executeQuery("SELECT R_Name FROM Region WHERE R_RegionKey ="+aux+"");
            if(rs.next()) {
                region = rs.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
        java.sql.PreparedStatement query1 = access.getSelect2();
        for (int i=0; i < times; i++) {
            try {
                long time = System.currentTimeMillis();
                query1.setInt(1, size);
                query1.setString(2, type);
                query1.setString(3, region);
                query1.setString(4, region);
                ResultSet rs = query1.executeQuery();
                /*while(rs.next()) {
                    System.out.println(rs.getObject(1));
                            
                }*/
                long finalTime = System.currentTimeMillis()-time;
                if(minTime == 0)
                    minTime = finalTime;
                else if(minTime >finalTime)
                    minTime = finalTime;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return minTime;
    }
      public long executeQuery3(int times) {
        long minTime = 0;
        String segment = "";
        java.sql.Date Date1 = null;
        Date Date2 = null;
        try {
            Statement s = access.getCon().createStatement();
            s = access.getCon().createStatement();
            int aux = r.nextInt(minCustomer);
            ResultSet rs = s.executeQuery("SELECT C_MktSegment FROM Customer WHERE C_CUSTKEY ="+aux+"");
            if(rs.next()) {
                segment = rs.getString(1);
            }
            s = access.getCon().createStatement();
            int aux2 = 0;
            //aux = r.nextInt(minOrders);
            rs = s.executeQuery("SELECT O_OrderKey,O_OrderDate FROM Orders WHERE O_CustKey ="+aux+"");
            if(rs.next()) {
                aux2 = rs.getInt(1);
                Date1 = rs.getDate(2);
            }
            rs = s.executeQuery("SELECT L_ShipDate FROM lineitem WHERE L_OrderKey ="+aux2+"");
            if(rs.next()) {
                Date2 = rs.getDate(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
        java.sql.PreparedStatement query1 = access.getSelect3();
        for (int i=0; i < times; i++) {
            try {
                long time = System.currentTimeMillis();
                query1.setString(1, segment);
                query1.setDate(2, Date1);
                query1.setDate(3, Date2);
                ResultSet rs = query1.executeQuery();
                /*while(rs.next()) {
                    System.out.println(rs.getObject(1));
                            
                }*/
                long finalTime = System.currentTimeMillis()-time;
                if(minTime == 0)
                    minTime = finalTime;
                else if(minTime >finalTime)
                    minTime = finalTime;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return minTime;
    }
        public long executeQuery4(int times) {
        long minTime = 0;
        String region = "";
        java.sql.Date Date1 = null;
        java.sql.Date Date2 = null;
        try {
            Statement s = access.getCon().createStatement();
            s = access.getCon().createStatement();
            int aux = r.nextInt(minRegion);
            ResultSet rs = s.executeQuery("SELECT R_Name FROM Region WHERE R_RegionKey ="+aux+"");
            if(rs.next()) {
                region = rs.getString(1);
            }
            s = access.getCon().createStatement();
            int aux2 = 0;
            aux = r.nextInt(minOrders);
            rs = s.executeQuery("SELECT O_OrderKey,O_OrderDate FROM Orders WHERE O_OrderKey ="+aux+"");
            if(rs.next()) {
                aux2 = rs.getInt(1);
                Date1 = rs.getDate(2);
            }
            aux = r.nextInt(minOrders);
            rs = s.executeQuery("SELECT O_OrderKey,O_OrderDate FROM Orders WHERE O_OrderKey ="+aux+"");
            if(rs.next()) {
                aux2 = rs.getInt(1);
                Date2 = rs.getDate(2);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
        java.sql.PreparedStatement query1 = access.getSelect4();
        for (int i=0; i < times; i++) {
            try {
                long time = System.currentTimeMillis();
                query1.setString(1, region);
                query1.setDate(2, Date1);
                query1.setDate(3, Date2);
                ResultSet rs = query1.executeQuery();
                /*while(rs.next()) {
                    System.out.println(rs.getObject(1));
                            
                }*/
                long finalTime = System.currentTimeMillis()-time;
                if(minTime == 0)
                    minTime = finalTime;
                else if(minTime >finalTime)
                    minTime = finalTime;
            } catch (SQLException ex) {
                Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return minTime;
    }
        
    private String generateRandomString(int lenght) {
        char[] chars = "abcdefghijklmnopqrstuvwxyz".toCharArray();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < lenght; i++) {
            char c = chars[r.nextInt(chars.length)];
            sb.append(c);
        }
        return sb.toString();
    }
    private void obtenerMinimos() {
        Statement m;
        try {
            m = access.getCon().createStatement();
            ResultSet rs;
            
            rs = m.executeQuery("SELECT MAX(C_CustKey) FROM Customer");
            if(rs.next())
                minCustomer = rs.getInt(1)+1;
            else
                minCustomer = 1;
            
            rs = m.executeQuery("SELECT MAX(N_NationKey) FROM Nation");
            if(rs.next())
                minNation = rs.getInt(1)+1;
            else
                minNation = 1;

            rs = m.executeQuery("SELECT MAX(O_OrderKey) FROM Orders");
            if(rs.next())
                minOrders = rs.getInt(1)+1;
            else
                minOrders = 1;
            
            rs = m.executeQuery("SELECT MAX(P_PartKey) FROM Part");
            if(rs.next())
                minPart = rs.getInt(1)+1;
            else
                minPart = 1;
            
            rs = m.executeQuery("SELECT MAX(R_RegionKey) FROM Region");
            if(rs.next())
                minRegion = rs.getInt(1)+1;
            else
                minRegion = 1;
             
            rs = m.executeQuery("SELECT MAX(S_SuppKey) FROM Supplier");
            if(rs.next())
                minSupplier = rs.getInt(1)+1;
            else
                minSupplier = 1;
            
            access.getCon().commit();
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }    
    }
    private String generateRandomDate2(Integer day, Integer month, Integer year) {
        String date ="";
        if(month == 2 && day >28)
            day = 28;
        if(day < 10)
            date = "0"+day.toString();
        else
            date = day.toString();
        date +="/";
        if(month < 10)
            date += "0"+month.toString();
        else
            date += month.toString();
        date +="/";
        if(year<10)
            date += "0"+year.toString();
        else
            date += year.toString();
        return date;
    }
    public void TruncateAll(){
        Statement m;
        CallableStatement q;
        try {
            /*m = access.getCon().prepareCall("Begin for t in (select table_name from user_tables)"
                    + " loop execute immediate "
                    + "('drop table '||t.table_name||' cascade constraints'); "
                    + "end loop");*/
            //q.executeUpdate();
            m = access.getCon().createStatement();
            
            /*
            m.executeUpdate("truncate table lineitem");
            m.executeUpdate("truncate table partSupp");
            m.executeUpdate("truncate table Part");
            m.executeUpdate("truncate table Supplier");
            m.executeUpdate("truncate table Customer");
            m.executeUpdate("truncate table Nation");
            m.executeUpdate("truncate table Region");*/
            m.executeUpdate("delete from lineitem");
            m.executeUpdate("delete from orders");
            m.executeUpdate("delete from partSupp");
            m.executeUpdate("delete from Supplier");
            m.executeUpdate("delete from Customer");
            m.executeUpdate("delete from Part");
            m.executeUpdate("delete from Nation");
            m.executeUpdate("delete from Region");
            access.getCon().commit();
            System.out.println("    BORRADO DE TABLAS HECHO");
            minRegion=1;
            minCustomer=1;
            minNation=1;
            minPart=1;
            minSupplier=1;
            minOrders = 1;
            PartSuppArray.clear();
        } catch (SQLException ex) {
            Logger.getLogger(OracleWorkFlow.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
